package com.lwj.file.mapper;


import com.lwj.file.entity.Order;
import com.lwj.file.entity.OrderDTO;
import lombok.SneakyThrows;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.*;


@Repository
public class OrderDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public List<Order> queryByScrollingPagination(long lastBatchMaxId,
                                                  int limit,
                                                  LocalDateTime paymentDateTimeStart,
                                                  LocalDateTime paymentDateTimeEnd) {
        return jdbcTemplate.query("SELECT id,creator,editor ,version,deleted,order_id,amount,order_status FROM t_order WHERE id > ? AND payment_time >= ? AND payment_time <= ? " +
                        "ORDER BY id ASC LIMIT ?",
                p -> {
                    p.setLong(1, lastBatchMaxId);
                    p.setTimestamp(2, Timestamp.valueOf(paymentDateTimeStart));
                    p.setTimestamp(3, Timestamp.valueOf(paymentDateTimeEnd));
                    p.setInt(4, limit);
                },
                rs -> {
                    List<Order> orders = new ArrayList<>();
                    while (rs.next()) {
                        Order order = new Order();
                        order.setId(rs.getLong("id"));
                        order.setCreator(rs.getString("creator"));
                        order.setEditor(rs.getString("editor"));
                        order.setVersion(rs.getLong("version"));
                        order.setDeleted(rs.getInt("deleted"));
                        order.setOrderId(rs.getString("order_id"));
                        order.setAmount(rs.getBigDecimal("amount"));
                        order.setOrderStatus(rs.getInt("order_status"));
                        orders.add(order);
                    }
                    return orders;
                });
    }

    public List<Order> queryByScrollingPagination(long lastBatchMaxId,
                                                  int limit) {
        return jdbcTemplate.query("SELECT id,creator,editor ,version,deleted,order_id,amount,order_status FROM t_order WHERE id > ? " +
                        "ORDER BY id ASC LIMIT ?",
                p -> {
                    p.setLong(1, lastBatchMaxId);
                    p.setInt(2, limit);
                },
                rs -> {
                    List<Order> orders = new ArrayList<>();
                    while (rs.next()) {
                        Order order = new Order();
                        order.setId(rs.getLong("id"));
                        order.setCreator(rs.getString("creator"));
                        order.setEditor(rs.getString("editor"));
                        order.setVersion(rs.getLong("version"));
                        order.setDeleted(rs.getInt("deleted"));
                        order.setOrderId(rs.getString("order_id"));
                        order.setAmount(rs.getBigDecimal("amount"));
                        order.setOrderStatus(rs.getInt("order_status"));
                        orders.add(order);
                    }
                    return orders;
                });
    }

    @SneakyThrows
    public void batchSave(List<OrderDTO> cachedDataList) {
        //结果集中数据为0时,结束方法.进行下一次调用
        if (cachedDataList.size() == 0) {
            return ;
        }
        //JDBC分批插入+事务操作完成对10w数据的插入
        Connection conn = null;
        PreparedStatement ps = null;
        try {
           conn = jdbcTemplate.getDataSource().getConnection();
            //控制事务:默认不提交
            conn.setAutoCommit(false);
            String sql = "INSERT INTO `execldemo`.`t_order` (`creator`, `order_id`, `amount`) VALUES ";
            sql += "(?,?,?)";
            ps = conn.prepareStatement(sql);
            for (OrderDTO orderDTO : cachedDataList) {
                ps.setString(1, orderDTO.getCreator());
                ps.setString(2, orderDTO.getOrderId());
                ps.setBigDecimal(3, orderDTO.getAmount());
                //将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }

            //执行批处理
             Arrays.stream(ps.executeBatch()).sum();
            //手动提交事务
            conn.commit();


        } catch (Exception e) {

            e.printStackTrace();
        } finally {
            conn.close();

        }
    }


}

